/*
 * 访问excel文件
 * 将数据读入mysql
 * 
 * 未来需要考虑Excel03-07公共接口的问题
 */


package dao;

import interfacePack.ITableObserver;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.PrepayRecordItem;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;



import context.Context;
import context.ExcelConnection;
import exception.ViewException;

public class Excel03DAO  {
	
	public static ArrayList<ITableObserver> observers = new ArrayList<ITableObserver>();
	
	public static void register(ITableObserver observer){
		observers.add(observer);
	}
	
	//检查excel文件是否为标准形式：只检查是否包含9列以及相应内容
	public static boolean checkHead(HSSFRow head) {
		String[] titleCN = {"流水号","交易日期","交易时间","发起机构","接受机构","卡号","交易金额","交易状态","交易码"};
		String[] titleEN = {"tradeNO","tradeDate","tradeTime","fromParty","toParty","cardID","amount","tradeState","tradeType"};
		for(int i = 0; i < titleCN.length; i++) {
			if(!head.getCell(i).toString().equals(titleCN[i]) && !head.getCell(i).toString().equals(titleEN[i]))
				return false;
			if(head.getCell(i).toString().equals(titleCN[i])) {
				head.getCell(i).setCellValue(titleEN[i]);
			}
		}
		return true;
	}
	
	public static boolean checkHeadNew(HSSFRow head) {
		String[] titleEN = {"tradeNo","ID","tradeDate","amount","tradeType"};
		for(int i = 0; i < titleEN.length; i++) {
			if(!head.getCell(i).toString().trim().equals(titleEN[i]))
				return false;
		}
		return true;
	}
	

	public static HSSFSheet getExcelRecord() throws ViewException{

		ExcelConnection connection = ExcelConnection.newInstance();

		HSSFWorkbook wb = null; 

		HSSFSheet contents = null;

		// 打开.xls文件
		try {

			POIFSFileSystem exlf = new POIFSFileSystem(
					new FileInputStream(connection.getAddr()));

			wb = new HSSFWorkbook(exlf);

		} catch (FileNotFoundException e) {

			e.printStackTrace();
			throw new ViewException("找不到文件");


		} catch (IOException e) {


			e.printStackTrace();
			throw new ViewException("读取文件出错");


		}

		// 取第一个sheet
		contents = wb.getSheetAt(0);
		return contents;
	}
	
	public static void importData(String tableName) throws ViewException{
		HSSFSheet contents;
		try {
			contents = getExcelRecord();
		} catch (ViewException e) {
			throw e;
		}

		int minColIdx, maxColIdx, maxRowIdx;

		maxRowIdx = contents.getLastRowNum();

		HSSFRow xlrow = contents.getRow(0);
		
		//check head:检查导入表是否为标准格式
		if(!checkHead(xlrow)) {
			throw new ViewException("文件不是标准格式");
		}

		// -----------------------建立MySQL连接-------------------------
		try {

			// 建立一个mysql的driver的实例，并将其注册到DriversManager
			Class.forName("com.mysql.jdbc.Driver");

		} catch (ClassNotFoundException e) {

			e.printStackTrace();
			throw new ViewException("连接mysql出错，请添加mysql jdbc driver");
		}

		Context c = Context.newInstance();

		try {

			// 建立连接
			Connection con = DriverManager.getConnection(c.getConnection(), c.getUsrName(), c.getPsw());

			Statement stmt = con
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);

			try {

				/*
				 * 建表 stmt.execute("create table ORGDATAS( " +
				 * "USERID VARCHAR(24) NOT NULL PRIMARY KEY," +
				 * " USERNAME VARCHAR(24), SEX TINYINT, " +
				 * "PASSWORD VARCHAR(64), USERTYPE TINYINT, " +
				 * "FREEAUTHEN TINYINT, CERTIFICATETYPE TINYINT, " +
				 * "CERTIFICATENO VARCHAR(24), EDUCATION TINYINT, " +
				 * "POSTCODE VARCHAR(10), ADDRESS VARCHAR(128), " +
				 * "PHONENO VARCHAR(24), BIRTHDAY DATE, EMAIL VARCHAR(64) );");
				 */
				HSSFCell cel, refcell;

				HSSFRow ferr = contents.getRow(1);

				String dropsql = "drop table if exists " + tableName + "; ";
				stmt.execute(dropsql);
				String ctbsql = "create table " + tableName + " (";

				for (int i = xlrow.getFirstCellNum(); i < xlrow
						.getLastCellNum(); i++) {

					cel = xlrow.getCell(i);

					refcell = ferr.getCell(i);


					if (refcell == null) {

						ctbsql = ctbsql.concat(cel.getStringCellValue()
								+ " VARCHAR(64)");

					} 
					else {
						
						if(!(cel.getStringCellValue().equals("amount") || cel.getStringCellValue().equals("交易金额"))) {
							ctbsql = ctbsql.concat(cel.getStringCellValue()
									+ " VARCHAR(64)");
						}
						else
							ctbsql = ctbsql.concat(cel.getStringCellValue()
									+ " decimal(10,2)");
						/*

						switch (refcell.getCellType()) {

						case Cell.CELL_TYPE_FORMULA:

						case Cell.CELL_TYPE_STRING:
							ctbsql = ctbsql.concat(cel.getStringCellValue()
									+ " VARCHAR(64)");
							break;

						case Cell.CELL_TYPE_NUMERIC:
							ctbsql = ctbsql.concat(cel.getStringCellValue()
									+ " INT");
							break;

						case Cell.CELL_TYPE_BOOLEAN:
							ctbsql = ctbsql.concat(cel.getStringCellValue()
									+ " TINYINT");
							break;

						default:
							ctbsql = ctbsql.concat(cel.getStringCellValue()
									+ " VARCHAR(64)");

						}*/

					}

					if (i < xlrow.getLastCellNum() - 1) {

						if (i == 0) {

							ctbsql = ctbsql.concat(" NOT NULL PRIMARY KEY");

						}

						ctbsql = ctbsql.concat(", ");

					} else {

						ctbsql = ctbsql.concat(");");

					}

				}

				System.out.println(ctbsql);
				stmt.execute(ctbsql);
				// 跟据前两行的内容来建表

			} catch (SQLException e) {
				e.printStackTrace();
				throw new ViewException(e.toString());
				//throw new ViewException("创建表时出错！请确认与数据库正确连接");

			}
			
			//创建视图
			CreateView.createViews(tableName);
			

			ResultSet rs = stmt.executeQuery("select * from NEWPAYRECORD");
			

			// 设定每列的最小最大索引
			minColIdx = xlrow.getFirstCellNum();

			maxColIdx = xlrow.getLastCellNum();

			int cnt = 0;

			boolean infirstrow = true;

			// for each式遍历整个表
			for (Row row : contents) {
				
				if (infirstrow) {

					infirstrow = false;

					continue;

				}
				rs.moveToInsertRow();


				ArrayList<String> cells = new ArrayList<String>();
				
				for (Cell cell : row) {

					if (cell == null) {

						continue;

					}
					cells.add(cell.toString());
					
					switch (cell.getCellType()) {

					case Cell.CELL_TYPE_FORMULA:

					case Cell.CELL_TYPE_STRING:
						//awful! 如果是amount列，取数字
//						if(cell.getColumnIndex() == 6) {
//							rs.updateDouble(cell.getColumnIndex()+1, cell.getNumericCellValue());
//						}
//						else
							rs.updateString(cell.getColumnIndex() + 1, cell
									.getStringCellValue());
						break;

					case Cell.CELL_TYPE_NUMERIC:
						rs.updateDouble(cell.getColumnIndex() + 1, cell
								.getNumericCellValue());
						break;

					case Cell.CELL_TYPE_BOOLEAN:
						rs.updateShort(cell.getColumnIndex() + 1, (short) cell
								.getNumericCellValue());
						break;

					default:
						rs.updateString(cell.getColumnIndex() + 1, cell
								.getStringCellValue());

					}

				}

				for(ITableObserver observer : observers){
					observer.update(cells);
				}
				
				rs.insertRow();

			}
			for(ITableObserver observer : observers){
				observer.updateFinished();
			}


		} catch (SQLException e) {

			e.printStackTrace();
			throw new ViewException(e.toString());

//			while (e != null) {
//
//
//
//				e = e.getNextException();
//
//				e.printStackTrace();
//
//
//			}

		} catch (IllegalStateException ie) {

			ie.printStackTrace();
			throw new ViewException(ie.toString());

		}

	}

	
	/*
	 * 按照新模板标准写的导入函数，包含列为：
	 * "tradeNo","cardID","tradeDate","tradeTime","amount","tradeType"
	 */
	public static void importDataNew(String tableName) throws ViewException{
		HSSFSheet contents;
		try {
			contents = getExcelRecord();
		} catch (ViewException e) {
			throw e;
		}

		int minColIdx, maxColIdx, maxRowIdx;

		maxRowIdx = contents.getLastRowNum();

		HSSFRow xlrow = contents.getRow(0);
		//String tableName = contents.getSheetName();
	
		
		//check head:检查导入表是否为标准格式
		if(!checkHeadNew(xlrow)) {
			throw new ViewException("文件不是标准格式");
		}

		// -----------------------建立MySQL连接-------------------------
		try {

			// 建立一个mysql的driver的实例，并将其注册到DriversManager
			Class.forName("com.mysql.jdbc.Driver");

		} catch (ClassNotFoundException e) {

			e.printStackTrace();
			throw new ViewException("连接mysql出错，请添加mysql jdbc driver");
		}

		Context c = Context.newInstance();

		try {

			// 建立连接
			Connection con = DriverManager.getConnection(c.getConnection(), c.getUsrName(), c.getPsw());

			Statement stmt = con
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);

			try {

				/*
				 * 建表 stmt.execute("create table ORGDATAS( " +
				 * "USERID VARCHAR(24) NOT NULL PRIMARY KEY," +
				 * " USERNAME VARCHAR(24), SEX TINYINT, " +
				 * "PASSWORD VARCHAR(64), USERTYPE TINYINT, " +
				 * "FREEAUTHEN TINYINT, CERTIFICATETYPE TINYINT, " +
				 * "CERTIFICATENO VARCHAR(24), EDUCATION TINYINT, " +
				 * "POSTCODE VARCHAR(10), ADDRESS VARCHAR(128), " +
				 * "PHONENO VARCHAR(24), BIRTHDAY DATE, EMAIL VARCHAR(64) );");
				 */
				HSSFCell cel, refcell;

				HSSFRow ferr = contents.getRow(1);

				String dropsql = "drop table if exists " + tableName + "; ";
				stmt.execute(dropsql);
				String ctbsql = "create table " + tableName + " (";

				for (int i = xlrow.getFirstCellNum(); i < xlrow
						.getLastCellNum(); i++) {

					cel = xlrow.getCell(i);

					refcell = ferr.getCell(i);


					if (refcell == null) {

						ctbsql = ctbsql.concat(cel.getStringCellValue()
								+ " VARCHAR(64)");

					} 
					else {
						
						if(!(cel.getStringCellValue().equals("amount") )) {
							ctbsql = ctbsql.concat(cel.getStringCellValue()
									+ " VARCHAR(64)");
						}
						else
							ctbsql = ctbsql.concat(cel.getStringCellValue()
									+ " decimal(10,2)");
						

					}

					if (i < xlrow.getLastCellNum() - 1) {

						if (i == 0) {

							ctbsql = ctbsql.concat(" NOT NULL PRIMARY KEY");

						}

						ctbsql = ctbsql.concat(", ");

					} else {

						ctbsql = ctbsql.concat(");");

					}

				}

				System.out.println(ctbsql);
				stmt.execute(ctbsql);
				// 跟据前两行的内容来建表

			} catch (SQLException e) {
				e.printStackTrace();
				throw new ViewException(e.toString());
				//throw new ViewException("创建表时出错！请确认与数据库正确连接");

			}
			
//			//创建视图
//			CreateView.createViews(tableName);
			

			ResultSet rs = stmt.executeQuery("select * from "+tableName);
			

			// 设定每列的最小最大索引
			minColIdx = xlrow.getFirstCellNum();

			maxColIdx = xlrow.getLastCellNum();

			int cnt = 0;

			boolean infirstrow = true;

			// for each式遍历整个表
			for (Row row : contents) {
				
				if (infirstrow) {

					infirstrow = false;

					continue;

				}
				rs.moveToInsertRow();


				ArrayList<String> cells = new ArrayList<String>();
				
				for (Cell cell : row) {

					if (cell == null) {

						continue;

					}
					cells.add(cell.toString());
					
					switch (cell.getCellType()) {

					case Cell.CELL_TYPE_FORMULA:

					case Cell.CELL_TYPE_STRING:
						rs.updateString(cell.getColumnIndex() + 1, cell
								.getStringCellValue());
						break;

					case Cell.CELL_TYPE_NUMERIC:
						rs.updateDouble(cell.getColumnIndex() + 1, cell
								.getNumericCellValue());
						break;

					case Cell.CELL_TYPE_BOOLEAN:
						rs.updateShort(cell.getColumnIndex() + 1, (short) cell
								.getNumericCellValue());
						break;

					default:
						rs.updateString(cell.getColumnIndex() + 1, cell
								.getStringCellValue());

					}

				}

				for(ITableObserver observer : observers){
					observer.update(cells);
				}
				rs.insertRow();

			}
			for(ITableObserver observer : observers){
				observer.updateFinished();
			}


		} catch (SQLException e) {

			e.printStackTrace();
			throw new ViewException(e.toString());

//			while (e != null) {
//
//
//
//				e = e.getNextException();
//
//				e.printStackTrace();
//
//
//			}

		} catch (IllegalStateException ie) {

			ie.printStackTrace();
			throw new ViewException(ie.toString());

		}

	}
}
